"""
数据同步业务服务层
实现同步任务的CRUD操作、执行管理等业务逻辑
"""

import math
from datetime import datetime
from typing import Optional, List, Dict, Any
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, and_, or_, func, desc, update
from sqlalchemy.orm import selectinload
from fastapi import HTTPException, status
import logging

from app.models.auth import User
from app.utils.timezone_utils import get_shanghai_now
from app.models.sync import (
    SyncTask, SyncExecution, SyncLog, SyncFieldMapping,
    ExecutionStatus, ExecutionType, SyncType, TargetType
)
from app.schemas.sync import (
    SyncTaskCreate, SyncTaskUpdate, SyncTaskResponse, SyncTaskListResponse,
    SyncExecutionCreate, SyncExecutionUpdate, SyncExecutionResponse, SyncExecutionListResponse,
    SyncLogResponse, SyncLogListResponse,
    SyncExecutionRequest, BatchSyncTaskRequest, BatchSyncTaskResponse
)
from app.services.sync_engine import SyncEngine

logger = logging.getLogger(__name__)


class SyncService:
    """数据同步服务类"""
    
    async def get_sync_tasks(
        self,
        db: AsyncSession,
        page: int = 1,
        page_size: int = 10,
        search: Optional[str] = None,
        sync_type: Optional[SyncType] = None,
        target_type: Optional[TargetType] = None,
        is_active: Optional[bool] = None,
        current_user: User = None
    ) -> SyncTaskListResponse:
        """获取同步任务列表"""
        try:
            # 构建查询条件
            conditions = [SyncTask.is_deleted == False]
            
            if search:
                search_condition = or_(
                    SyncTask.name.ilike(f"%{search}%"),
                    SyncTask.display_name.ilike(f"%{search}%"),
                    SyncTask.description.ilike(f"%{search}%")
                )
                conditions.append(search_condition)
            
            if sync_type:
                conditions.append(SyncTask.sync_type == sync_type)
            
            if target_type:
                conditions.append(SyncTask.target_type == target_type)
            
            if is_active is not None:
                conditions.append(SyncTask.is_active == is_active)
            
            # 构建查询
            query = select(SyncTask).where(and_(*conditions))
            
            # 添加关联加载
            query = query.options(
                selectinload(SyncTask.field_mappings),
                selectinload(SyncTask.source_datasource),
                selectinload(SyncTask.target_datasource)
            )
            
            # 获取总数
            count_query = select(func.count(SyncTask.id)).where(and_(*conditions))
            total_result = await db.execute(count_query)
            total = total_result.scalar()
            
            # 分页查询
            offset = (page - 1) * page_size
            query = query.order_by(desc(SyncTask.created_at)).offset(offset).limit(page_size)
            
            result = await db.execute(query)
            tasks = result.scalars().all()
            
            # 转换为响应模型
            task_responses = [SyncTaskResponse.from_orm(task) for task in tasks]
            
            return SyncTaskListResponse(
                items=task_responses,
                total=total,
                page=page,
                page_size=page_size,
                pages=math.ceil(total / page_size) if total > 0 else 0
            )
            
        except Exception as e:
            logger.error(f"获取同步任务列表失败: {e}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="获取同步任务列表失败"
            )
    
    async def get_sync_task(
        self,
        db: AsyncSession,
        task_id: int,
        current_user: User = None
    ) -> Optional[SyncTaskResponse]:
        """获取单个同步任务"""
        try:
            query = select(SyncTask).where(
                SyncTask.id == task_id,
                SyncTask.is_deleted == False
            ).options(
                selectinload(SyncTask.field_mappings),
                selectinload(SyncTask.source_datasource),
                selectinload(SyncTask.target_datasource)
            )
            
            result = await db.execute(query)
            task = result.scalar_one_or_none()
            
            if not task:
                return None
            
            return SyncTaskResponse.from_orm(task)
            
        except Exception as e:
            logger.error(f"获取同步任务失败: {e}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="获取同步任务失败"
            )
    
    async def create_sync_task(
        self,
        db: AsyncSession,
        task_data: SyncTaskCreate,
        current_user: User
    ) -> SyncTaskResponse:
        """创建同步任务"""
        try:
            # 检查任务名称是否已存在
            existing_task = await db.execute(
                select(SyncTask).where(
                    SyncTask.name == task_data.name,
                    SyncTask.is_deleted == False
                )
            )
            
            if existing_task.scalar_one_or_none():
                raise HTTPException(
                    status_code=status.HTTP_400_BAD_REQUEST,
                    detail=f"任务名称 '{task_data.name}' 已存在"
                )
            
            # 创建同步任务
            task = SyncTask(
                name=task_data.name,
                display_name=task_data.display_name,
                description=task_data.description,
                source_datasource_id=task_data.source_datasource_id,
                source_table_name=task_data.source_table_name,
                source_query=task_data.source_query,
                target_type=task_data.target_type,
                target_datasource_id=task_data.target_datasource_id,
                target_table_name=task_data.target_table_name,
                target_path=task_data.target_path,
                sync_type=task_data.sync_type,
                incremental_field=task_data.incremental_field,
                incremental_field_type=task_data.incremental_field_type,
                batch_size=task_data.batch_size,
                schedule_type=task_data.schedule_type,
                cron_expression=task_data.cron_expression,
                is_active=task_data.is_active,
                created_by=current_user.id,
                updated_by=current_user.id
            )
            
            db.add(task)
            await db.flush()  # 获取任务ID
            
            # 创建字段映射
            if task_data.field_mappings:
                for mapping_data in task_data.field_mappings:
                    mapping = SyncFieldMapping(
                        task_id=task.id,
                        source_field=mapping_data.source_field,
                        target_field=mapping_data.target_field,
                        field_type=mapping_data.field_type,
                        transform_expression=mapping_data.transform_expression,
                        is_key_field=mapping_data.is_key_field,
                        sort_order=mapping_data.sort_order
                    )
                    db.add(mapping)
            
            await db.commit()
            
            # 重新查询以获取完整数据
            return await self.get_sync_task(db, task.id, current_user)
            
        except HTTPException:
            raise
        except Exception as e:
            await db.rollback()
            logger.error(f"创建同步任务失败: {e}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="创建同步任务失败"
            )
    
    async def update_sync_task(
        self,
        db: AsyncSession,
        task_id: int,
        task_data: SyncTaskUpdate,
        current_user: User
    ) -> Optional[SyncTaskResponse]:
        """更新同步任务"""
        try:
            # 获取现有任务
            task = await db.execute(
                select(SyncTask).where(
                    SyncTask.id == task_id,
                    SyncTask.is_deleted == False
                )
            )
            task = task.scalar_one_or_none()
            
            if not task:
                return None
            
            # 更新任务字段（排除不应该被更新的字段）
            excluded_fields = {
                'field_mappings',  # 单独处理
                'is_deleted', 'deleted_at', 'deleted_by',  # 逻辑删除相关
                'created_by', 'created_at',  # 创建时设置，不应更新
                'updated_by', 'updated_at',  # 在下面单独处理
                'id',  # 主键，不应更新
                'last_execution_id', 'last_execution_time', 'last_execution_status', 'next_execution_time'  # 系统自动维护
            }

            update_data = task_data.dict(exclude_unset=True, exclude=excluded_fields)

            for field, value in update_data.items():
                if hasattr(task, field):
                    setattr(task, field, value)
            
            task.updated_by = current_user.id
            task.updated_at = get_shanghai_now()
            
            # 更新字段映射
            if task_data.field_mappings is not None:
                # 删除现有映射（SyncFieldMapping没有逻辑删除，直接物理删除）
                from sqlalchemy import delete
                await db.execute(
                    delete(SyncFieldMapping)
                    .where(SyncFieldMapping.task_id == task_id)
                )
                
                # 创建新映射
                for mapping_data in task_data.field_mappings:
                    mapping = SyncFieldMapping(
                        task_id=task.id,
                        source_field=mapping_data.source_field,
                        target_field=mapping_data.target_field,
                        field_type=mapping_data.field_type,
                        transform_expression=mapping_data.transform_expression,
                        is_key_field=mapping_data.is_key_field,
                        sort_order=mapping_data.sort_order
                    )
                    db.add(mapping)
            
            await db.commit()

            # 重新查询以获取完整数据
            return await self.get_sync_task(db, task_id, current_user)
            
        except Exception as e:
            await db.rollback()
            logger.error(f"更新同步任务失败: {e}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="更新同步任务失败"
            )
    
    async def delete_sync_task(
        self,
        db: AsyncSession,
        task_id: int,
        current_user: User
    ) -> bool:
        """删除同步任务（逻辑删除）"""
        try:
            task = await db.execute(
                select(SyncTask).where(
                    SyncTask.id == task_id,
                    SyncTask.is_deleted == False
                )
            )
            task = task.scalar_one_or_none()
            
            if not task:
                return False
            
            # 检查是否有正在运行的执行
            running_execution = await db.execute(
                select(SyncExecution).where(
                    SyncExecution.task_id == task_id,
                    SyncExecution.status == ExecutionStatus.RUNNING
                )
            )
            
            if running_execution.scalar_one_or_none():
                raise HTTPException(
                    status_code=status.HTTP_400_BAD_REQUEST,
                    detail="无法删除正在运行的同步任务"
                )
            
            # 逻辑删除
            task.is_deleted = True
            task.deleted_at = get_shanghai_now()
            task.deleted_by = current_user.id
            
            await db.commit()
            return True
            
        except HTTPException:
            raise
        except Exception as e:
            await db.rollback()
            logger.error(f"删除同步任务失败: {e}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="删除同步任务失败"
            )
    
    async def execute_sync_task(
        self,
        db: AsyncSession,
        request: SyncExecutionRequest,
        current_user: User
    ) -> SyncExecutionResponse:
        """执行同步任务"""
        try:
            # 获取任务
            task = await db.execute(
                select(SyncTask).where(
                    SyncTask.id == request.task_id,
                    SyncTask.is_deleted == False,
                    SyncTask.is_active == True
                )
            )
            task = task.scalar_one_or_none()
            
            if not task:
                raise HTTPException(
                    status_code=status.HTTP_404_NOT_FOUND,
                    detail="同步任务不存在或已禁用"
                )
            
            # 检查是否有正在运行的执行
            running_execution = await db.execute(
                select(SyncExecution).where(
                    SyncExecution.task_id == request.task_id,
                    SyncExecution.status == ExecutionStatus.RUNNING
                )
            )
            
            if running_execution.scalar_one_or_none():
                raise HTTPException(
                    status_code=status.HTTP_400_BAD_REQUEST,
                    detail="任务正在执行中，请稍后再试"
                )
            
            # 准备执行参数
            execution_params = request.execution_params or {}

            # 如果用户指定了自定义增量值，添加到执行参数中
            if request.custom_incremental_value is not None:
                execution_params['custom_incremental_value'] = request.custom_incremental_value

            # 创建执行记录
            execution = SyncExecution(
                task_id=request.task_id,
                execution_type=ExecutionType.MANUAL,
                execution_params=execution_params,
                created_by=current_user.id
            )
            
            db.add(execution)
            await db.commit()
            
            # 异步执行同步任务
            # 为后台任务创建新的数据库会话，避免会话生命周期冲突
            import asyncio
            from app.core.database import AsyncSessionLocal

            async def background_sync_task():
                async with AsyncSessionLocal() as background_db:
                    # 在新的会话中重新获取执行记录
                    background_execution = await background_db.get(SyncExecution, execution.id)
                    if background_execution:
                        sync_engine = SyncEngine(background_db)
                        await sync_engine.execute_sync_task(task, background_execution)

            asyncio.create_task(background_sync_task())
            
            return SyncExecutionResponse.from_orm(execution)
            
        except HTTPException:
            raise
        except Exception as e:
            logger.error(f"执行同步任务失败: {e}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="执行同步任务失败"
            )

    async def pause_sync_execution(
        self,
        db: AsyncSession,
        execution_id: int,
        current_user: User
    ) -> bool:
        """暂停同步执行"""
        try:
            # 获取执行记录
            execution = await self.get_sync_execution(db, execution_id, current_user)

            # 检查状态是否可以暂停
            if execution.status != ExecutionStatus.RUNNING:
                return False

            # 更新状态为暂停
            execution.status = ExecutionStatus.PAUSED
            execution.updated_at = get_shanghai_now()

            await db.commit()

            # 通知同步引擎暂停任务
            from app.services.sync_engine import SyncEngine
            sync_engine = SyncEngine(db)
            await sync_engine.pause_execution(execution_id)

            return True

        except Exception as e:
            await db.rollback()
            logger.error(f"暂停同步执行失败: {str(e)}")
            return False

    async def cancel_sync_execution(
        self,
        db: AsyncSession,
        execution_id: int,
        current_user: User
    ) -> bool:
        """取消同步执行"""
        try:
            # 获取执行记录
            execution = await self.get_sync_execution(db, execution_id, current_user)

            # 检查状态是否可以取消
            if execution.status not in [ExecutionStatus.RUNNING, ExecutionStatus.PAUSED]:
                return False

            # 更新状态为取消
            execution.status = ExecutionStatus.CANCELLED
            execution.completed_at = get_shanghai_now()
            execution.updated_at = get_shanghai_now()

            # 计算执行时间
            if execution.started_at:
                execution_time = (execution.completed_at - execution.started_at).total_seconds() * 1000
                execution.execution_time_ms = int(execution_time)

            await db.commit()

            # 通知同步引擎取消任务
            from app.services.sync_engine import SyncEngine
            sync_engine = SyncEngine(db)
            await sync_engine.cancel_execution(execution_id)

            return True

        except Exception as e:
            await db.rollback()
            logger.error(f"取消同步执行失败: {str(e)}")
            return False

    async def get_sync_execution(
        self,
        db: AsyncSession,
        execution_id: int,
        current_user: User = None
    ) -> SyncExecution:
        """获取单个同步执行记录"""
        try:
            # 构建查询
            query = select(SyncExecution).where(SyncExecution.id == execution_id)

            result = await db.execute(query)
            execution = result.scalar_one_or_none()

            if not execution:
                raise HTTPException(
                    status_code=status.HTTP_404_NOT_FOUND,
                    detail="执行记录不存在"
                )

            return execution

        except HTTPException:
            raise
        except Exception as e:
            logger.error(f"获取同步执行记录失败: {e}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="获取同步执行记录失败"
            )

    async def get_sync_executions(
        self,
        db: AsyncSession,
        task_id: Optional[int] = None,
        status: Optional[ExecutionStatus] = None,
        page: int = 1,
        page_size: int = 10,
        current_user: User = None
    ) -> SyncExecutionListResponse:
        """获取同步执行记录列表"""
        try:
            # 构建查询条件
            conditions = []
            
            if task_id:
                conditions.append(SyncExecution.task_id == task_id)
            
            if status:
                conditions.append(SyncExecution.status == status)
            
            # 构建查询
            query = select(SyncExecution)
            if conditions:
                query = query.where(and_(*conditions))
            
            # 获取总数
            count_query = select(func.count(SyncExecution.id))
            if conditions:
                count_query = count_query.where(and_(*conditions))
            
            total_result = await db.execute(count_query)
            total = total_result.scalar()
            
            # 分页查询
            offset = (page - 1) * page_size
            query = query.order_by(desc(SyncExecution.created_at)).offset(offset).limit(page_size)
            
            result = await db.execute(query)
            executions = result.scalars().all()
            
            # 转换为响应模型
            execution_responses = [SyncExecutionResponse.from_orm(execution) for execution in executions]
            
            return SyncExecutionListResponse(
                items=execution_responses,
                total=total,
                page=page,
                page_size=page_size,
                pages=math.ceil(total / page_size) if total > 0 else 0
            )
            
        except Exception as e:
            logger.error(f"获取同步执行记录失败: {e}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="获取同步执行记录失败"
            )
    
    async def get_sync_logs(
        self,
        db: AsyncSession,
        execution_id: int,
        page: int = 1,
        page_size: int = 50,
        current_user: User = None
    ) -> SyncLogListResponse:
        """获取同步日志列表"""
        try:
            # 构建查询
            query = select(SyncLog).where(SyncLog.execution_id == execution_id)
            
            # 获取总数
            count_query = select(func.count(SyncLog.id)).where(SyncLog.execution_id == execution_id)
            total_result = await db.execute(count_query)
            total = total_result.scalar()
            
            # 分页查询
            offset = (page - 1) * page_size
            query = query.order_by(SyncLog.created_at).offset(offset).limit(page_size)
            
            result = await db.execute(query)
            logs = result.scalars().all()
            
            # 转换为响应模型
            log_responses = [SyncLogResponse.from_orm(log) for log in logs]
            
            return SyncLogListResponse(
                items=log_responses,
                total=total,
                page=page,
                page_size=page_size,
                pages=math.ceil(total / page_size) if total > 0 else 0
            )
            
        except Exception as e:
            logger.error(f"获取同步日志失败: {e}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="获取同步日志失败"
            )
    



# 创建全局同步服务实例
sync_service = SyncService()
